package com.dashboard.giangday;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.lb.jwitter.connectDatabase.ConnectTTGD;
import com.opensymphony.xwork2.Action;

public class ThongKeSoLan_AllKhoaAction {
	private String jsonData;
	public String getJsonData() {
		return jsonData;
	}


	public void setJsonData(String jsonData) {
		this.jsonData = jsonData;
	}
	////////////////////////////////////////////////////////
	private String namHoc;

	public String getNamHoc() {
		return namHoc;
	}


	public void setNamHoc(String namHoc) {
		this.namHoc = namHoc;
	}
	/////////////////////////////////////////////////////////
	private String hocKy;

	public String getHocKy() {
		return hocKy;
	}


	public void setHocKy(String hocKy) {
		this.hocKy = hocKy;
	}
	/////////////////////////////////////////////////////////
	private ArrayList<ThongKe> listThongKe;
	
	public String execute() throws Exception
	{
		ConnectTTGD connectionOracle= new ConnectTTGD();
		if(connectionOracle.getConnection()!=null){
			listThongKe=new ArrayList<ThongKe>();
			//namHoc="2013-2014";
			//hocKy="1";
			
			//if(namHoc==null||namHoc.length()<1){
			//	return Action.ERROR;
			//}
			
			if(hocKy==null||hocKy.length()<1){
				return Action.ERROR;
			}
			
			//hocKy  20141
			int nam = Integer.parseInt(hocKy.substring(0, 4));
			namHoc = nam+"-"+ (nam+1);
			hocKy = hocKy.substring(4);
			
			int tongSolan=0;
			int chuadb=0;
			int dunggio=0;
			String query="";
			
			
			//Tinh so lan som/tre, nghi day co bao, nghi day khong bao, day bu cho tung khoa
			
			 query="select maKhoa,tenKhoa,maGhichu, count(maGhichu) as solan  from sukiengiangday_view"
					 +" where namhoc='2013-2014' and hocky=1  and maGhichu<>'DTCB' and maGhichu<>'DTKB'"
					 +" group by maGhichu,maKhoa,tenKhoa"
					 +" order by maKhoa,maGhichu,solan";
			
			
			ResultSet data=connectionOracle.getData(query);
			if(data!=null){
				ThongKe thongke;
				while(data.next()){
					String maKhoa=data.getString("maKhoa");
					String tenKhoa=data.getString("tenKhoa");
					String ghiChu=data.getString("maGhichu");
					int solan=data.getInt("solan");
					int index=getThongKeKhoaByMaKhoa(maKhoa);
					
					if(index>=0){
						thongke=listThongKe.get(index);
						//So lan day bu
						if(ghiChu.equals("DB")){
							thongke.setDb(solan);
						
						}
						//So lan nghi day co bao
						else if(ghiChu.equals("NDCB")){
							thongke.setNdcb(solan);
							
						}
						
						//So lan nghi day khong bao
						else if(ghiChu.equals("NDKB")){
							thongke.setNdkb(solan);		
						}
						
						//So lan vao tre
						else if(ghiChu.equals("VT")){
							thongke.setSomTre(thongke.getSomTre()+solan);
							
						}
						
						//So lan ve som
						else if(ghiChu.equals("RS")){
							thongke.setSomTre(thongke.getSomTre()+solan);
						}
						
						//So lan nghi giai lao qua gio
						else if(ghiChu.equals("NGLQG")){
							thongke.setSomTre(thongke.getSomTre()+solan);
						}
					}
					else{
						thongke=new ThongKe();
						thongke.setMaKhoa(maKhoa);
						thongke.setTenKhoa(tenKhoa);
						
						//So lan day bu
						if(ghiChu.equals("DB")){
							thongke.setDb(solan);
						
						}
						//So lan nghi day co bao
						else if(ghiChu.equals("NDCB")){
							thongke.setNdcb(solan);
							
						}
						
						//So lan nghi day khong bao
						else if(ghiChu.equals("NDKB")){
							thongke.setNdkb(solan);		
						}
						
						//So lan vao tre
						else if(ghiChu.equals("VT")){
							thongke.setSomTre(thongke.getSomTre()+solan);
							
						}
						
						//So lan ve som
						else if(ghiChu.equals("RS")){
							thongke.setSomTre(thongke.getSomTre()+solan);
						}
						
						//So lan nghi giai lao qua gio
						else if(ghiChu.equals("NGLQG")){
							thongke.setSomTre(thongke.getSomTre()+solan);
						}
						listThongKe.add(thongke);
					}
					
					
				}
				//Tinh so lan chua day bu
				
				
			}
			
			
			query="select sum(tongSoBuoi) as tongSolan, maKhoa from"
					+" (select ((A.sotietMonhoc/A.sotietTrongTuan)* A.soBuoiTrongTuan) as tongSoBuoi, maKhoa from"
					+" (select   maMonHoc, tenMonhoc,sotietMonhoc,lophoc, maCb,tenCb,sum(sotiet) as sotietTrongTuan, count(thu) as soBuoiTrongTuan, maKhoa"
					+" from thoikhoabieu_view"
					+" where namhoc='2013-2014' and hocky=1"
					+" group by  maMonHoc, tenMonhoc,sotietMonhoc,lophoc, maCb,tenCb, maKhoa"
					+" order by maMonHoc,maCb,lophoc,thu) A) B group by maKhoa";
			
			
			data=connectionOracle.getData(query);
			if(data!=null){
				while(data.next()){
					String maKhoa=data.getString("maKhoa");
					//Tinh tong so buoi len lop cua cac giang vien
					tongSolan=data.getInt("tongSolan");
					int index=getThongKeKhoaByMaKhoa(maKhoa);
					if(index>=0){
						listThongKe.get(index).setTongSolan(tongSolan);
					}
				}
			}
			
			jsonData="[";
			for(int index=0;index<listThongKe.size();index++){
				ThongKe thongke=listThongKe.get(index);
				dunggio= thongke.getTongSolan()-thongke.getSomTre();
				//thongke.setDunggio(dunggio);
				chuadb= (thongke.getNdcb()+thongke.getNdkb())-thongke.getDb();
				if(chuadb<0){
					chuadb=0;
				}
				//thongke.setChuadb(chuadb);
				jsonData=jsonData+"{\"maKhoa\":\""+thongke.getMaKhoa()+"\",\"tenKhoa\":\""+thongke.getTenKhoa()+"\","
						+"\"tongSolan\":"+thongke.getTongSolan()+",\"dungGio\":"+dunggio+",\"somTre\":"+thongke.getSomTre()+","
						+"\"NDCB\":"+thongke.getNdcb()+",\"NDKB\":"+thongke.getNdkb()+",\"DB\":"+thongke.getDb()+","
						+"\"CDB\":"+chuadb+"},";
			}
			jsonData=jsonData+"]";
			jsonData=jsonData.replaceAll(",]", "]");
			
			
		}
		connectionOracle.closeConnection();
		
		return Action.SUCCESS;

	}
	private int getThongKeKhoaByMaKhoa(String maKhoa){
		for(int index=0;index<listThongKe.size();index++){
			if(listThongKe.get(index).getMaKhoa().equals(maKhoa)){
				return index;
			}
		}
		return -1;
	}
}
